﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Data.SqlClient;
using YiTu.DBUtility;
namespace TOUR.SZHOME.DAL
{
    /// <summary>
    /// 数据表Landscape的数据操作类
    /// </summary>
    public partial class LandscapeDAL
    {
        #region ConstVariables
        private const string C_TABLE_NAME = "Landscape";
        private const string C_SP_LANDSCAPE_FIELDS = "[JQID],[JQMC],[JQMS],[XCLX],[XGT],[SYXM],[SFTJ],[XMLB]";
        private const string C_SP_LANDSCAPE_INSERT = "INSERT INTO [Landscape]([JQMC],[JQMS],[XCLX],[XGT],[SYXM],[SFTJ],[XMLB]) VALUES(@JQMC,@JQMS,@XCLX,@XGT,@SYXM,@SFTJ,@XMLB);SET @JQID = SCOPE_IDENTITY();";
        private const string C_SP_LANDSCAPE_UPDATE = "UPDATE [Landscape] SET [JQMC]=@JQMC,[JQMS]=@JQMS,[XCLX]=@XCLX,[XGT]=@XGT,[SYXM]=@SYXM,[SFTJ]=@SFTJ,[XMLB]=@XMLB WHERE [JQID] = @JQID";
        private const string C_SP_LANDSCAPE_DELETE = "DELETE [Landscape] WHERE [JQID] = @JQID";
        private const string C_SP_LANDSCAPE_GET = "SELECT [JQID],[JQMC],[JQMS],[XCLX],[XGT],[SYXM],[SFTJ],[XMLB] FROM [Landscape] WHERE [JQID] = @JQID";
        #endregion

        private static string ConnectionString
        {
            get
            {
                return System.Configuration.ConfigurationManager.ConnectionStrings["TravelProject"].ConnectionString;
            }
        }

        /// <summary>
        /// 默认构造函数
        /// </summary>
        private LandscapeDAL() { }

        /// <summary>
        /// 向数据表中插入一条新记录
        /// </summary>
        /// <param name="entity">Entity.LandscapeEntity实体类</param>
        /// <remarks>如果表存在自增长字段，插入记录成功后自增长字段值会更新至实体</remarks>
        public static bool Insert(Entity.LandscapeEntity entity)
        {
            List<SqlParameter> commandParms = new List<SqlParameter>();
            SqlParameter id_JQID = SqlHelper.CreateParam("@JQID", SqlDbType.Int, 0, ParameterDirection.Output, null);
            commandParms.Add(id_JQID);
            commandParms.Add(SqlHelper.CreateParam("@JQMC", SqlDbType.NVarChar, 50, ParameterDirection.Input, entity.JQMC));
            commandParms.Add(SqlHelper.CreateParam("@JQMS", SqlDbType.NVarChar, 4000, ParameterDirection.Input, entity.JQMS));
            commandParms.Add(SqlHelper.CreateParam("@XCLX", SqlDbType.NVarChar, 4000, ParameterDirection.Input, entity.XCLX));
            commandParms.Add(SqlHelper.CreateParam("@XGT", SqlDbType.NVarChar, 200, ParameterDirection.Input, entity.XGT));
            commandParms.Add(SqlHelper.CreateParam("@SYXM", SqlDbType.NVarChar, 500, ParameterDirection.Input, entity.SYXM));
            commandParms.Add(SqlHelper.CreateParam("@SFTJ", SqlDbType.NVarChar, 10, ParameterDirection.Input, entity.SFTJ));
            commandParms.Add(SqlHelper.CreateParam("@XMLB", SqlDbType.NVarChar, 50, ParameterDirection.Input, entity.XMLB));

            return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, C_SP_LANDSCAPE_INSERT, commandParms) > 0 ? true : false;
            //entity.JQID = Convert.ToInt32(id_JQID.Value);
        }

        /// <summary>
        /// 获取数据库一条记录实体(根据主键条件)
        /// </summary>
        /// <param name="jQID">景区ID</param>
        /// <returns>Entity.LandscapeEntity实体类</returns>
        public static Entity.LandscapeEntity GetByJQID(int jQID)
        {
            Entity.LandscapeEntity entity = null;
            List<SqlParameter> commandParms = new List<SqlParameter>();
            commandParms.Add(SqlHelper.CreateParam("@JQID", SqlDbType.Int, 0, ParameterDirection.Input, jQID));

            using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, CommandType.Text, C_SP_LANDSCAPE_GET, commandParms))
            {
                if (reader.Read())
                {
                    entity = ConvertToEntityFromDataReader(reader);
                }
            }

            return entity;
        }

        /// <summary>
        /// 更新数据库中一条记录(根据主键条件)
        /// </summary>
        /// <param name="entity">Entity.LandscapeEntity实体类</param>
        public static bool Update(Entity.LandscapeEntity entity)
        {
            List<SqlParameter> commandParms = new List<SqlParameter>();

            commandParms.Add(SqlHelper.CreateParam("@JQID", SqlDbType.Int, 0, ParameterDirection.Input, entity.JQID));
            commandParms.Add(SqlHelper.CreateParam("@JQMC", SqlDbType.NVarChar, 50, ParameterDirection.Input, entity.JQMC));
            commandParms.Add(SqlHelper.CreateParam("@JQMS", SqlDbType.NVarChar, 4000, ParameterDirection.Input, entity.JQMS));
            commandParms.Add(SqlHelper.CreateParam("@XCLX", SqlDbType.NVarChar, 4000, ParameterDirection.Input, entity.XCLX));
            commandParms.Add(SqlHelper.CreateParam("@XGT", SqlDbType.NVarChar, 200, ParameterDirection.Input, entity.XGT));
            commandParms.Add(SqlHelper.CreateParam("@SYXM", SqlDbType.NVarChar, 500, ParameterDirection.Input, entity.SYXM));
            commandParms.Add(SqlHelper.CreateParam("@SFTJ", SqlDbType.NVarChar, 10, ParameterDirection.Input, entity.SFTJ));
            commandParms.Add(SqlHelper.CreateParam("@XMLB", SqlDbType.NVarChar, 50, ParameterDirection.Input, entity.XMLB));

            return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, C_SP_LANDSCAPE_UPDATE, commandParms) > 0 ? true : false;
        }

        /// <summary>
        /// 删除数据库中一条记录(根据主键条件)
        /// </summary>
        /// <param name="jQID">景区ID</param>
        public static bool Delete(int jQID)
        {
            List<SqlParameter> commandParms = new List<SqlParameter>();
            commandParms.Add(SqlHelper.CreateParam("@JQID", SqlDbType.Int, 0, ParameterDirection.Input, jQID));
            return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, C_SP_LANDSCAPE_DELETE, commandParms) > 0 ? true : false;
        }

        /// <summary>
        /// 按条件查询数据表,返回DataTable类型数据
        /// </summary>
        /// <param name="whereClause">SQL条件语句(可为空)，不须带"Where"关键字</param>
        /// <param name="dataFields">需返回字段(不能为空,为"*"则返回所有字段)</param>
        /// <param name="orderBy">SQL排序语句(不能为空)，不须带"Order By"关键字</param>
        /// <param name="startRowIndex">记录开始索引，从0开始</param>
        /// <param name="maximumRows">返回记录数量</param>
        /// <returns>DataTable</returns>
        public static DataTable SearchDT(string whereClause, string dataFields, string orderBy, int startRowIndex, int maximumRows)
        {
            if (dataFields.Trim() == "*")
            {
                dataFields = C_SP_LANDSCAPE_FIELDS;
            }
            return SqlListHepler.Search(ConnectionString, C_TABLE_NAME, dataFields, whereClause, orderBy, startRowIndex, maximumRows);
        }


        /// <summary>
        /// 按条件查询数据表,返回 Entity.LandscapeEntity 数据集
        /// </summary>
        /// <param name="whereClause">SQL条件语句(可为空)，不须带"Where"关键字</param>
        /// <param name="orderBy">SQL排序语句(不能为空)，不须带"Order By"关键字</param>
        /// <param name="startRowIndex">记录开始索引，从0开始</param>
        /// <param name="maximumRows">返回记录数量</param>
        public static List<Entity.LandscapeEntity> Search(string whereClause, string orderBy, int startRowIndex, int maximumRows)
        {
            List<Entity.LandscapeEntity> list = new List<Entity.LandscapeEntity>();
            using (SqlDataReader reader = SqlListHepler.SearchDataReader(ConnectionString, C_TABLE_NAME, C_SP_LANDSCAPE_FIELDS, whereClause, orderBy, startRowIndex, maximumRows))
            {
                while (reader.Read())
                {
                    list.Add(ConvertToEntityFromDataReader(reader));
                }
            }
            return list;
        }

        /// <summary>
        /// 按条件查询数据表,返回DataTable类型数据
        /// </summary>
        /// <param name="whereClause">SQL条件语句(可为空)，不须带"Where"关键字</param>
        /// <param name="dataFields">需返回字段(不能为空,为"*"则返回所有字段)</param>
        /// <param name="orderBy">SQL排序语句(可为空)，不须带"Order By"关键字</param>
        /// <param name="rowsToReturn">返回记录数量</param>
        /// <returns>DataTable</returns>
        public static DataTable SearchDT(string whereClause, string dataFields, string orderBy, int rowsToReturn)
        {
            if (dataFields.Trim() == "*")
            {
                dataFields = C_SP_LANDSCAPE_FIELDS;
            }
            return SqlListHepler.Search(ConnectionString, C_TABLE_NAME, dataFields, whereClause, orderBy, rowsToReturn);
        }

        /// <summary>
        /// 按条件查询数据表,返回 Entity.LandscapeEntity 数据集
        /// </summary>
        /// <param name="whereClause">SQL条件语句(可为空)，不须带"Where"关键字</param>
        /// <param name="orderBy">SQL排序语句(可为空)，不须带"Order By"关键字</param>
        /// <param name="rowsToReturn">返回记录数量</param>
        public static List<Entity.LandscapeEntity> Search(string whereClause, string orderBy, int rowsToReturn)
        {
            List<Entity.LandscapeEntity> list = new List<Entity.LandscapeEntity>();
            using (SqlDataReader reader = SqlListHepler.SearchDataReader(ConnectionString, C_TABLE_NAME, C_SP_LANDSCAPE_FIELDS, whereClause, orderBy, rowsToReturn))
            {
                while (reader.Read())
                {
                    list.Add(ConvertToEntityFromDataReader(reader));
                }
            }
            return list;
        }

        /// <summary>
        /// 按条件获取记录数量
        /// </summary>
        /// <param name="whereClause">SQL条件语句(可为空)，不须带"Where"关键字</param>
        /// <returns>int整型数据</returns>
        public static int SearchCount(string whereClause)
        {
            return SqlListHepler.SearchCount(ConnectionString, C_TABLE_NAME, whereClause);
        }

        /// <summary>
        /// 转换DataRow类型数据记录为实体
        /// </summary>
        private static Entity.LandscapeEntity ConvertToEntityFromDataRow(DataRow row)
        {
            Entity.LandscapeEntity entity = new Entity.LandscapeEntity();
            entity.JQID = Convert.ToInt32(row["JQID"]);
            entity.JQMC = row["JQMC"].ToString();
            if (Convert.IsDBNull(row["JQMS"])) entity.JQMS = null; else entity.JQMS = row["JQMS"].ToString();
            if (Convert.IsDBNull(row["XCLX"])) entity.XCLX = null; else entity.XCLX = row["XCLX"].ToString();
            if (Convert.IsDBNull(row["XGT"])) entity.XGT = null; else entity.XGT = row["XGT"].ToString();
            entity.SYXM = row["SYXM"].ToString();
            if (Convert.IsDBNull(row["SFTJ"])) entity.SFTJ = null; else entity.SFTJ = row["SFTJ"].ToString();
            if (Convert.IsDBNull(row["XMLB"])) entity.XMLB = null; else entity.XMLB = row["XMLB"].ToString();

            return entity;
        }

        /// <summary>
        /// 转换SqlDataReader类型数据记录为实体
        /// </summary>
        private static Entity.LandscapeEntity ConvertToEntityFromDataReader(SqlDataReader reader)
        {
            Entity.LandscapeEntity entity = new Entity.LandscapeEntity();
            entity.JQID = Convert.ToInt32(reader["JQID"]);
            entity.JQMC = reader["JQMC"].ToString();
            if (Convert.IsDBNull(reader["JQMS"])) entity.JQMS = null; else entity.JQMS = reader["JQMS"].ToString();
            if (Convert.IsDBNull(reader["XCLX"])) entity.XCLX = null; else entity.XCLX = reader["XCLX"].ToString();
            if (Convert.IsDBNull(reader["XGT"])) entity.XGT = null; else entity.XGT = reader["XGT"].ToString();
            entity.SYXM = reader["SYXM"].ToString();
            if (Convert.IsDBNull(reader["SFTJ"])) entity.SFTJ = null; else entity.SFTJ = reader["SFTJ"].ToString();
            if (Convert.IsDBNull(reader["XMLB"])) entity.XMLB = null; else entity.XMLB = reader["XMLB"].ToString();

            return entity;
        }

    }
}
